from openpyxl import load_workbook
import xmind
from xmind.core.markerref import MarkerId
import re

def excel_to_xmind(excel_path, xmind_path, model):
    # 加载Excel工作簿
    wb = load_workbook(excel_path)
    ws = wb.active

    # 创建XMind工作簿
    xmind_workbook = xmind.load("temp.xmind")
    sheet = xmind_workbook.getPrimarySheet()
    sheet.setTitle("测试设计思维脑图")
    root_topic = sheet.getRootTopic()
    root_topic.setTitle("测试设计思维脑图")

    # 创建自定义节点
    custom_topic = root_topic.addSubTopic()
    custom_topic.setTitle(f"自定义:{model}")

    # 获取列索引
    headers = {}
    header_row = ws[1]
    for cell in header_row:
        if cell.value:
            header_name = cell.value.strip().replace(" ", "").lower()
            headers[header_name] = cell.column

    # 定义实际需要的列名（标准化后的）
    required_columns = {
        "编号": "编号",
        "用例等级": "用例等级",
        "标题": "标题",
        "前提条件": "前提条件",
        "测试步骤": "测试步骤",
        "预期结果": "预期结果"
    }

    # 检查所有必要列是否存在
    missing_columns = []
    for col_name, col_key in required_columns.items():
        standardized = col_key.strip().replace(" ", "").lower()
        if standardized not in headers:
            missing_columns.append(col_name)

    if missing_columns:
        print(f"错误: Excel文件中缺少以下必要列: {', '.join(missing_columns)}")
        return

    # 等级到标记的映射
    level_to_marker = {
        "高": "#0",
        "中": "#1",
        "低": "#2",
        "建议": "#3"
    }

    # 处理每一行用例数据（从第2行开始）
    for row in range(2, ws.max_row + 1):
        # 获取用例ID
        case_id = ws.cell(row=row, column=headers["编号"]).value

        # 跳过无效行
        if not case_id:
            continue

        # 获取其他数据
        priority = ws.cell(row=row, column=headers["用例等级"]).value or ""
        title = ws.cell(row=row, column=headers["标题"]).value or "未命名用例"
        precondition = ws.cell(row=row, column=headers["前提条件"]).value
        steps = ws.cell(row=row, column=headers["测试步骤"]).value
        expected = ws.cell(row=row, column=headers["预期结果"]).value

        # 创建用例主题
        case_topic = custom_topic.addSubTopic()

        # 构建标题（去除序号）
        clean_title = re.sub(r'^\d+\.?\s*', '', str(title)).strip()

        # 添加等级标记
        level_marker = level_to_marker.get(priority, "")
        if level_marker:
            case_title = f"用例:{clean_title}{level_marker}"
        else:
            case_title = f"用例:{clean_title}"

        case_topic.setTitle(case_title)

        # 添加前置条件子节点
        if precondition:
            precondition_topic = case_topic.addSubTopic()
            clean_precondition = re.sub(r'^\d+\.?\s*', '', str(precondition)).strip()
            precondition_topic.setTitle(f"前置条件: {clean_precondition}")

        # 处理测试步骤和预期结果
        if steps or expected:
            # 处理测试步骤
            step_list = []
            if steps and isinstance(steps, str):
                steps_clean = steps.strip()
                if "\n" in steps_clean:
                    step_list = [s.strip() for s in steps_clean.split("\n") if s.strip()]
                elif re.search(r'\d+\.', steps_clean):
                    step_list = re.split(r'\d+\.', steps_clean)
                    step_list = [s.strip() for s in step_list if s.strip()]
                else:
                    step_list = [steps_clean]

            # 处理预期结果
            expected_list = []
            if expected and isinstance(expected, str):
                if "\n" in expected:
                    expected_list = [e.strip() for e in expected.split("\n") if e.strip()]
                else:
                    expected_list = [expected.strip()]

            # 确保两个列表长度一致
            max_length = max(len(step_list), len(expected_list))
            if len(step_list) < max_length:
                step_list += [""] * (max_length - len(step_list))
            if len(expected_list) < max_length:
                expected_list += [""] * (max_length - len(expected_list))

            # 添加步骤和预期
            for i, (step, exp) in enumerate(zip(step_list, expected_list), 1):
                if step or exp:
                    step_topic = case_topic.addSubTopic()

                    # 格式化步骤文本
                    clean_step = re.sub(r'^\d+\.?\s*', '', step).strip()
                    clean_exp = re.sub(r'^\d+\.?\s*', '', exp).strip()

                    # 创建步骤-预期节点
                    step_topic.setTitle(f"步骤: {clean_step}")
                    if clean_exp:
                        exp_topic = step_topic.addSubTopic()
                        exp_topic.setTitle(f"预期: {clean_exp}")

    # 保存XMind文件
    xmind.save(xmind_workbook, xmind_path)
    print(f"转换成功! XMind文件已保存至: {xmind_path}")


if __name__ == "__main__":
    # 配置路径
    input_excel = "../王者问答excel用例.xlsx"  # 输入的Excel文件名
    output_xmind = "王者问答excel用例.xmind"  # 输出的XMind文件名
    model = "王者问答"

    # 执行转换
    excel_to_xmind(input_excel, output_xmind, model)



